﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using YNCNumberSys.db;
using YNCNumberSys.Entity;

namespace YNCNumberSys.DbDal
{
    public class PaperCodeDal
    {
        private CommandManager commandManager = new CommandManager();
        private ConnectionManager connectionManager = new ConnectionManager();

        public DataTable GetDistinctWXDW()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(string.Format(@"select 
                              distinct [外协单位]
                             from 图纸编号表 where 外协=1 order by [外协单位]"));
            DataTable dt = commandManager.ExecuteNonQuery(connectionManager.getConnection(), sql.ToString(), "图纸编号表");
            return dt;
        }
        public DataTable GetDistinctJCXH()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(string.Format(@"select 
                              distinct [机床型号]
                             from 图纸编号表 order by [机床型号]"));
            DataTable dt = commandManager.ExecuteNonQuery(connectionManager.getConnection(), sql.ToString(), "图纸编号表");
            return dt;
        }

        public DataTable GetDistinctAH(string jcxh)
        {
            StringBuilder sql = new StringBuilder();
            if(!string.IsNullOrEmpty(jcxh))
               sql.Append(string.Format(@"select 
                              distinct [A号]
                             from 图纸编号表 where 机床型号 like '%{0}%' order by [A号]", jcxh));
            else
                sql.Append(string.Format(@"select 
                              distinct [A号]
                             from 图纸编号表  order by [A号]"));
            DataTable dt = commandManager.ExecuteNonQuery(connectionManager.getConnection(), sql.ToString(), "图纸编号表");
            return dt;
        }
        public DataTable GetPaperCode(PaperCodeEntity ent, CodeEntity codeEntity)
        {

            StringBuilder sql = new StringBuilder();
  //    string sql2 = @"SELECT [管路]
  //    ,[始端设备]
  //    ,[终端设备]
  //    ,[ID]
  //FROM [YNCNumberSys].[dbo].[Table_1]";
            sql.Append(string.Format(@"select [ID]
                              ,[机床型号]
                              ,[A号]
                              ,[零件序号]
                              ,[图纸类型]+right('0000000000'+cast([图纸编号] as nvarchar(50)),{0}) AS [图纸编号]
                              ,[名称]
                              ,[规格]
                              ,[申请人]
                              ,[审核人]
                              ,[申请日期]
                              ,[外协]
                              ,[外协单位]
                              ,[作废]
                              ,[图纸类型],[FC] AS [4C] from 图纸编号表 where ", codeEntity.Digit));
            if (ent.Wx != null)
            {
                sql.Append(string.Format(" 外协={0} and ", ent.Wx));
            }
            if (ent.Zf != null)
            {
                sql.Append(string.Format(" 作废={0} and ", ent.Zf));
            }
            if (!string.IsNullOrEmpty(ent.Zh))
            {
                sql.Append(string.Format(" [申请人]='{0}' and ", ent.Zh));
            }
            if (!string.IsNullOrEmpty(ent.Ah))
            {
                sql.Append(string.Format(" [A号]='{0}' and ", ent.Ah));
            }
            if (!string.IsNullOrEmpty(ent.Ljxh))
            {
                sql.Append(string.Format(" [零件序号]='{0}' and ", ent.Ljxh));
            }
            if (!string.IsNullOrEmpty(ent.Tzbh))
            {
                sql.Append(string.Format(" [图纸编号]='{0}' and ", ent.Tzbh));
            }
            if (!string.IsNullOrEmpty(ent.Mc))
            {
                sql.Append(string.Format(" [名称]='{0}' and ", ent.Mc));
            }
            if (!string.IsNullOrEmpty(ent.Wxdw))
            {
                sql.Append(string.Format(" [外协单位]='{0}' and ", ent.Wxdw));
            }
            if (!string.IsNullOrEmpty(ent.Tzlx))
            {
                sql.Append(string.Format(" [图纸类型]='{0}' and ", ent.Tzlx));
            }
            if (!string.IsNullOrEmpty(ent.Gz))
            {
                sql.Append(string.Format(" [规格]='{0}' and ", ent.Gz));
            }
            if (!string.IsNullOrEmpty(ent.Jcxh))
            {
                sql.Append(string.Format(" [机床型号]='{0}' and ", ent.Jcxh));
            }
            sql.Append(" 1=1 order by 机床型号 ASC,A号 ASC;");
            DataTable dt = commandManager.ExecuteNonQuery(connectionManager.getConnection(), sql.ToString(), "图纸编号表");
            return dt;
        }

        public DataTable GetPaperCodeLike(PaperCodeEntity ent, CodeEntity codeEntity)
        {

            StringBuilder sql = new StringBuilder();
            sql.Append(string.Format(@"select [ID]
                              ,[机床型号]
                              ,[A号]
                              ,[零件序号]
                              ,right('0000000000'+cast([图纸编号] as nvarchar(50)),{0}) AS [图纸编号]
                              ,[名称]
                              ,[规格]
                              ,[申请人]
                              ,[审核人]
                              ,[申请日期]
                              ,[外协]
                              ,[外协单位]
                              ,[作废]
                              ,[图纸类型] from 图纸编号表 where ", codeEntity.Digit));
            if (ent.Wx != null)
            {
                sql.Append(string.Format(" 外协={0} and ", ent.Wx));
            }
            if (ent.Zf != null)
            {
                sql.Append(string.Format(" 作废={0} and ", ent.Zf));
            }
            if (!string.IsNullOrEmpty(ent.Zh))
            {
                sql.Append(string.Format(" [申请人]='{0}' and ", ent.Zh));
            }
            if (!string.IsNullOrEmpty(ent.Ah))
            {
                sql.Append(string.Format(" [A号]='{0}' and ", ent.Ah));
            }
            if (!string.IsNullOrEmpty(ent.Ljxh))
            {
                sql.Append(string.Format(" [零件序号]='{0}' and ", ent.Ljxh));
            }
            if (!string.IsNullOrEmpty(ent.Tzbh))
            {
                sql.Append(string.Format(" [图纸编号]='{0}' and ", ent.Tzbh));
            }
            if (!string.IsNullOrEmpty(ent.Mc))
            {
                sql.Append(string.Format(" [名称]='{0}' and ", ent.Mc));
            }
            if (!string.IsNullOrEmpty(ent.Wxdw))
            {
                sql.Append(string.Format(" [外协单位]='{0}' and ", ent.Wxdw));
            }
            if (!string.IsNullOrEmpty(ent.Tzlx))
            {
                sql.Append(string.Format(" [图纸类型]='{0}' and ", ent.Tzlx));
            }
            if (!string.IsNullOrEmpty(ent.Gz))
            {
                sql.Append(string.Format(" [规格]='{0}' and ", ent.Gz));
            }
            if (!string.IsNullOrEmpty(ent.Jcxh))
            {
                //sql.Append(string.Format(" [机床型号]='{0}' and ", ent.Jcxh));
                sql.Append(string.Format(" [机床型号]like '%{0}%' and ", ent.Jcxh));
            }
            sql.Append(" 1=1 order by 机床型号 ASC,A号 ASC;");
            DataTable dt = commandManager.ExecuteNonQuery(connectionManager.getConnection(), sql.ToString(), "图纸编号表");
            return dt;
        }
        /// <summary>
        /// 插入【图号作废表】，更新【图纸编号表】
        /// </summary>
        /// <param name="sqlList"></param>
        /// <returns></returns>
        public int InsertZfPapercodeAndPapercode(List<string> sqlList)
        {
           return commandManager.update(connectionManager.getConnection(), sqlList);
        }
        /// <summary>
        /// 更新填写零部件[名称],材料[规格]
        /// </summary>
        /// <param name="paperCode">零件号</param>
        /// <param name="lbj">零部件名称</param>
        /// <param name="cl">材料规格</param>
        /// <returns></returns>
        public int UpdatePapercode(int paperCode,string lbj,string cl)
        {
            string sql = string.Format(@"UPDATE [YNCNumberSys].[dbo].[图纸编号表]
                           SET [名称] = '{0}'
                              ,[规格] = '{1}'
                         WHERE 图纸编号={2}", lbj, cl, paperCode);
            //函数中有事务
            return commandManager.update(connectionManager.getConnection(), sql);
        }

        /// <summary>
        /// 获取4C的表头。
        /// 这个条件一定是4C，不用后加的FC判断。
        /// </summary>
        /// <param name="sJch">机床号</param>
        /// <param name="sAh">A号</param>
        /// <returns></returns>
        public DataTable Get4CTitle(string sJch, string sAh)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(string.Format(@"select ID,[机床型号]
                                              ,[A号]
                                              ,[零件序号]
                                              ,convert(varchar(100),[图纸类型],20)+ RIGHT('000000000000' + CAST([图纸编号] AS VARCHAR(10)),6) [图纸编号]
                                              ,[名称]
                                              ,[规格]
                                              ,[申请人]
                                              ,[审核人]
                                              ,[申请日期]
                                              ,[外协]
                                              ,[外协单位]
                                              ,[作废]
                                              ,[图纸类型]
                            from 图纸编号表 where 
                                           机床型号 ='{0}' 
                                           and A号 ='{1}' 
                                           and ([零件序号]='ZP-4C' 
                                           and [图纸类型]='4C') ", sJch, sAh));
            DataTable dt = commandManager.ExecuteNonQuery(connectionManager.getConnection(), sql.ToString(), "图纸编号表");
            return dt;
        }
        
        public DataTable GetZpOr4C(string sJch,string sAh)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(string.Format(@"select ID,[机床型号]
                                              ,[A号]
                                              ,[零件序号]
                                              ,convert(varchar(100),[图纸类型],20)+ RIGHT('000000000000' + CAST([图纸编号] AS VARCHAR(10)),6) [图纸编号]
                                              ,[名称]
                                              ,[规格]
                                              ,[申请人]
                                              ,[审核人]
                                              ,[申请日期]
                                              ,[外协]
                                              ,[外协单位]
                                              ,[作废]
                                              ,[图纸类型]
                            from 图纸编号表 where 机床型号 ='{0}' and A号 ='{1}' and ([零件序号]='ZP-4C' and [图纸类型]='4C')", sJch, sAh));
            sql.Append(" OR ");
            sql.Append("([零件序号]='ZP' and [图纸类型]<>'4C');");
            DataTable dt = commandManager.ExecuteNonQuery(connectionManager.getConnection(), sql.ToString(), "图纸编号表");
            return dt;
        }

        /// <summary>
        /// 获取ZP的表头部分，这个逻辑好乱。
        /// </summary>
        /// <param name="sJch">机床号。</param>
        /// <param name="sAh">A号。</param>
        /// <returns></returns>
        public DataTable GetZpTitle(string sJch, string sAh)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(string.Format(@"select ID,[机床型号]
                                              ,[A号]
                                              ,[零件序号]
                                              ,convert(varchar(100),[图纸类型],20)+ RIGHT('000000000000' + CAST([图纸编号] AS VARCHAR(10)),6) [图纸编号]
                                              ,[名称]
                                              ,[规格]
                                              ,[申请人]
                                              ,[审核人]
                                              ,[申请日期]
                                              ,[外协]
                                              ,[外协单位]
                                              ,[作废]
                                              ,[图纸类型]
                            from 图纸编号表 where 机床型号 ='{0}' and A号 ='{1}' and ", sJch, sAh));
            //sql.Append("([零件序号]='ZP' and [图纸类型]<>'4C');");
            sql.Append("([零件序号]='ZP'  and (FC is null or FC=0));"); //FC is null or FC=0这个条件是非4C的。
            DataTable dt = commandManager.ExecuteNonQuery(connectionManager.getConnection(), sql.ToString(), "图纸编号表");
            return dt;
        }
        /// <summary>
        /// 获取非4C图的图号，多个，用于生成目录表。
        /// </summary>
        /// <param name="sJch">机床号。</param>
        /// <param name="sAh">A号。</param>
        /// <returns></returns>
        public DataTable GetZP(string sJch, string sAh)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(string.Format(@"select ID,[机床型号]
                                              ,[A号]
                                              ,[零件序号]
                                              ,convert(varchar(100),[图纸类型],20)+ RIGHT('000000000000' + CAST([图纸编号] AS VARCHAR(10)),6) [图纸编号]
                                              ,[名称]
                                              ,[规格]
                                              ,[申请人]
                                              ,[审核人]
                                              ,[申请日期]
                                              ,[外协]
                                              ,[外协单位]
                                              ,[作废]
                                              ,[图纸类型] 
                                       from 图纸编号表 where 
                                       机床型号 ='{0}' 
                                       and A号 ='{1}' 
                                       and [零件序号]<>'ZP' 
                                       and [零件序号]<>'ZP-4C' 
                                       and (FC is null or FC=0)
                                       order by 图纸编号 ASC;", sJch, sAh));
            DataTable dt = commandManager.ExecuteNonQuery(connectionManager.getConnection(), sql.ToString(), "图纸编号表");
            return dt;
        }

        /// <summary>
        /// 获4C图的图号，多个，用于生成目录表。
        /// </summary>
        /// <param name="sJch">机床号。</param>
        /// <param name="sAh">A号。</param>
        /// <returns></returns>
        public DataTable Get4C(string sJch, string sAh)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(string.Format(@"select ID,[机床型号]
                                              ,[A号]
                                              ,[零件序号]
                                              ,convert(varchar(100),[图纸类型],20)+ RIGHT('000000000000' + CAST([图纸编号] AS VARCHAR(10)),6) [图纸编号]
                                              ,[名称]
                                              ,[规格]
                                              ,[申请人]
                                              ,[审核人]
                                              ,[申请日期]
                                              ,[外协]
                                              ,[外协单位]
                                              ,[作废]
                                              ,[图纸类型]
                              from 图纸编号表 where 
                              机床型号 ='{0}' 
                              and A号 ='{1}' 
                              and [零件序号]<>'ZP-4C' 
                              and ([图纸类型]='4C' or FC=1) and [图纸类型]!='4C'  order by 图纸编号 ASC ;", sJch, sAh));
            //sql.Append(string.Format(@" select * from(select ID,[机床型号]
            //                                          ,[A号]
            //                                          ,[零件序号]
            //                                          ,convert(varchar(100),[图纸类型],20)+ RIGHT('000000000000' + CAST([图纸编号] AS VARCHAR(10)),6) [图纸编号]
            //                                          ,[名称]
            //                                          ,[规格]
            //                                          ,[申请人]
            //                                          ,[审核人]
            //                                          ,[申请日期]
            //                                          ,[外协]
            //                                          ,[外协单位]
            //                                          ,[作废]
            //                                          ,[图纸类型]
            //                                          ,2 sign
            //                            from 图纸编号表 where 
            //                            机床型号 ='{0}' 
            //                            and A号 ='{1}' 
            //                            --and [零件序号]<>'ZP-4C' 
            //                            and ([图纸类型]='4C' or FC=1) 
            //                             --order by 图纸编号 ASC 
            //                            UNION ALL
            //                            select ID,[机床型号]
            //                                          ,[A号]
            //                                          ,[零件序号]
            //                                          ,convert(varchar(100),[图纸类型],20)+ RIGHT('000000000000' + CAST([图纸编号] AS VARCHAR(10)),6) [图纸编号]
            //                                          ,[名称]
            //                                          ,[规格]
            //                                          ,[申请人]
            //                                          ,[审核人]
            //                                          ,[申请日期]
            //                                          ,[外协]
            //                                          ,[外协单位]
            //                                          ,[作废]
            //                                          ,[图纸类型]
            //                                           ,1 sign
            //                            from 图纸编号表 where 
            //                            机床型号 ='{0}' 
            //                            and A号 ='{1}' 
            //                            and [零件序号]<>'ZP-4C' 
            //                            and ([图纸类型] !='4C' or FC!=1)
            //                            ) t
            //                     order by t.sign ASC, t.图纸编号 ASC ", sJch, sAh));
            DataTable dt = commandManager.ExecuteNonQuery(connectionManager.getConnection(), sql.ToString(), "图纸编号表");
            return dt;
        }
    }
}
